package SiTuQingYou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import SiTuQingYou.util.DBHelper;
import SiTuQingYou.util.Pager;
import SiTuQingYou.vo.Scene;
import SiTuQingYou.vo.Strategy;
import SiTuQingYou.vo.User;

public class StrategyDao 
{
	private  StrategyDao() {}
	private static  StrategyDao sd = new  StrategyDao();
	public static  StrategyDao getsd()
	{
		return sd;
	}
	public ArrayList<Strategy> checkall(String name) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql = "select * from strategy,user where userid=user.id  and  strategy.fakedelete=0  and title like '%"+name+"%'";
			conn = DBHelper.getDB().getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			ArrayList<Strategy> str = new ArrayList<Strategy>();
			while(rs.next()) {
				   Strategy stra = new Strategy();
				   stra.setId(rs.getInt("id"));
				   stra.setTitle(rs.getString("title"));
				   stra.setContent(rs.getString("content"));
				   stra.setPhoto(rs.getString("photo"));
				   User user = new User();
				   user.setUsername(rs.getString("username"));
				   stra.setUser(user);
				   stra.setStime(rs.getTimestamp("stime"));
				   stra.setAuditstatus(rs.getInt("auditstatus"));
				   str.add(stra);
			}
			return str;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDB().close(rs, ps, conn);
		}
		return null;
	}
	
	//往数据库写入攻略
	public void writeStrategy(Strategy strategy)
	{
		System.out.println(strategy.getTitle());
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql="insert into strategy (userid,scenicspotid,photo,content,title,auditstatus) values(?,?,?,?,?,0)";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, strategy.getUser().getId());
			ps.setInt(2, strategy.getScene().getId());
			ps.setString(3, strategy.getPhoto());
			ps.setString(4, strategy.getContent());
			ps.setString(5, strategy.getTitle());
			ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		
	}
	 //假删除
	public void deletestr(int id) {
		    Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
				try {
					conn = DBHelper.getDB().getConn();
					String sql = "update strategy set fakedelete=1 where id=?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, id);
					ps.executeUpdate();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
					DBHelper.getDB().close(rs, ps, conn);
				}
	}
	
	public void passstr(int a,int id,String name) {
		  Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
				try {
					conn = DBHelper.getDB().getConn();
					String sql = "update strategy set auditstatus=?,thereason=? where id=?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, a);
					ps.setString(2, name);
					ps.setInt(3, id);
					ps.executeUpdate();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
					DBHelper.getDB().close(rs, ps, conn);
				}
	}
	//所有的待审核攻略
	  public ArrayList<Strategy>  showallaud() {
		  Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			
			try {
				conn = DBHelper.getDB().getConn();
				String sql = "select * from strategy,user where strategy.userid=user.id and  strategy.fakedelete=0  and auditstatus=0";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
			   ArrayList<Strategy> str =new  ArrayList<Strategy>();
			   while(rs.next()) {
				   Strategy stra = new Strategy();
				   stra.setId(rs.getInt("id"));
				   stra.setTitle(rs.getString("title"));
				   stra.setContent(rs.getString("content"));
				   stra.setPhoto(rs.getString("photo"));
				   User user = new User();
				   user.setUsername(rs.getString("username"));
				   stra.setUser(user);
				   stra.setStime(rs.getTimestamp("stime"));
				   stra.setAuditstatus(rs.getInt("auditstatus"));
				   str.add(stra);
			   }
			   return str;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getDB().close(rs, ps, conn);
			}
			return null;
	  }
	//多少个未审核的攻略
	public  int allauditing () {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
	int count = 0;
		try {
			conn = DBHelper.getDB().getConn();
			String sql = "select count(*)  num from strategy  where   strategy.fakedelete=0 and  auditstatus=0";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt("num");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getDB().close(rs, ps, conn);
		}
		
		return count;
	}
	
	
	//通过景点id找景点对应所有的攻略
	public ArrayList<Strategy> getAllStrategyBySceneId(int id)
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql="select * from strategy,user where strategy.scenicspotid=? and  strategy.fakedelete=0  and strategy.userid=user.id and auditstatus='1' ";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			ArrayList<Strategy> strategys =new ArrayList<Strategy>();
			
			while(rs.next())
			{
				Strategy strategy=new Strategy();
				strategy.setId(rs.getInt("id"));
				Scene scene=new Scene();
				scene.setId(rs.getInt("scenicspotid"));
				strategy.setScene(scene);
				strategy.setPhoto(rs.getString("photo"));
				strategy.setContent(rs.getString("content"));
				strategy.setTitle(rs.getString("title"));
				User user=new User();
				user.setId(rs.getInt("userid"));
				user.setUsernickname(rs.getString("usernickname"));
				strategy.setUser(user);
				strategys.add(strategy);
				
			}
			return strategys;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return null;
	}
	
	
	
	//得到数据库前三个攻略信息
	public ArrayList<Strategy> getThreeStrategy()
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql="select * from strategy,user where strategy.userid=user.id  and  strategy.fakedelete=0 and auditstatus='1' limit 0,3";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			ArrayList<Strategy> strategys =new ArrayList<Strategy>();
			
			while(rs.next())
			{
				Strategy strategy=new Strategy();
				strategy.setId(rs.getInt("id"));
				Scene scene=new Scene();
				scene.setId(rs.getInt("scenicspotid"));
				strategy.setScene(scene);
				strategy.setPhoto(rs.getString("photo"));
				strategy.setContent(rs.getString("content"));
				strategy.setTitle(rs.getString("title"));
				User user=new User();
				user.setId(rs.getInt("userid"));
				user.setUsernickname(rs.getString("usernickname"));
				strategy.setUser(user);
				strategys.add(strategy);
				
			}
			return strategys;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return null;
	}
	
	
	//ͨ������id��ù��Ե����ݣ��ڹ�����ϸ��Ϣ��չʾ
	public Strategy getContentByStrategyId(int id)
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql="select * from strategy,user where strategy.userid=user.id and auditstatus='1' and strategy.id=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			rs.next();
			Strategy strategy=new Strategy();
			User user=new User();
			user.setUsernickname(rs.getString("usernickname"));
			strategy.setTitle(rs.getString("title"));
			strategy.setContent(rs.getString("content"));
			strategy.setStime(rs.getTimestamp("stime"));
			strategy.setPhoto(rs.getString("photo"));
			strategy.setUser(user);
			strategy.setId(rs.getInt("id"));
			return strategy;
			
			} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return null;
	}
	   public int getcout() {
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			 int count = 0;
			 try {
				 conn = DBHelper.getDB().getConn();
				String sql = "select  COUNT(*)   as num from strategy"; 
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt("num");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			 finally {
				 DBHelper.getDB().close(rs, ps, conn);
			 }
			 return count;
	   }
	   //得到已经通过审核的攻略的总数
	   public int getCountPassStrategy() {
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			 int count = 0;
			 try {
				 conn = DBHelper.getDB().getConn();
				String sql = "select  COUNT(*)   as num from strategy where auditstatus='1'"; 
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt("num");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			 finally {
				 DBHelper.getDB().close(rs, ps, conn);
			 }
			 return count;
	   }
	//分页
	public ArrayList<Strategy> findAllStrategy(int pagenum)
	{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql="select * from strategy,user where strategy.userid=user.id  and  strategy.fakedelete=0   and auditstatus='1' limit ?,? ";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, (pagenum-1)*5);
			ps.setInt(2, 5);
			rs=ps.executeQuery();
			ArrayList<Strategy> strategys =new ArrayList<Strategy>();
			while(rs.next())
			{
				Strategy strategy=new Strategy();
				strategy.setId(rs.getInt("id"));
				Scene scene=new Scene();
				scene.setId(rs.getInt("scenicspotid"));
				strategy.setScene(scene);
				strategy.setTitle(rs.getString("title"));
				strategy.setPhoto(rs.getString("photo"));
				strategy.setContent(rs.getString("content"));
				User user=new User();
				user.setId(rs.getInt("userid"));
				user.setUsernickname(rs.getString("usernickname"));
				strategy.setUser(user);
				strategys.add(strategy);
			}
			return strategys;
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return null;
	}
	
	
	
	
	//所有的攻略
	public ArrayList<Strategy> getAllStrategy(Pager pager)
	{
		pager.setPageSize(Integer.parseInt("5"));
		pager.setDataCount(this.getCountPassStrategy());
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getDB().getConn();
			String sql="select * from strategy,user where strategy.userid=user.id and  strategy.fakedelete=0  and auditstatus='1' limit ?,?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,pager.getFirstRow());
			ps.setInt(2,pager.getPageSize());
			rs=ps.executeQuery();
			ArrayList<Strategy> strategys =new ArrayList<Strategy>();
			while(rs.next())
			{
				Strategy strategy=new Strategy();
				strategy.setId(rs.getInt("id"));
				Scene scene=new Scene();
				scene.setId(rs.getInt("scenicspotid"));
				strategy.setScene(scene);
				strategy.setTitle(rs.getString("title"));
				strategy.setPhoto(rs.getString("photo"));
				strategy.setContent(rs.getString("content"));
				User user=new User();
				user.setId(rs.getInt("userid"));
				user.setUsernickname(rs.getString("usernickname"));
				strategy.setUser(user);
				strategys.add(strategy);
			}
			return strategys;
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return null;
	}
	
	
	
	//通过用户id，查询出他所写的所有攻略（已审核、未审核、审核不通过的全部显示）
    public ArrayList<Strategy> getAllWritedStrategyByUser(int userid,Pager pager){
    	pager.setDataCount(this.getTheCountOfWrited(userid));
    	
    	Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		ArrayList<Strategy> writedstr =new ArrayList<Strategy>();
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql = "select * from strategy,scene where userid=? and scenicspotid=scene.id "
					+ "and strategy.fakedelete=0 order by stime desc limit ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setInt(2, pager.getFirstRow());//获得limit后面第一个数是几
			ps.setInt(3, pager.getPageSize());//获得每页几条记录
			rs = ps.executeQuery();
			while(rs.next()) {
				Strategy str = new Strategy();
				str.setId(rs.getInt("strategy.id"));
				str.setPhoto(rs.getString("photo"));//攻略图片
				str.setContent(rs.getString("content"));//攻略内容
				str.setTitle(rs.getString("title"));//攻略标题
				str.setStime(rs.getTimestamp("stime"));//攻略发表时间
				str.setAuditstatus(rs.getInt("auditstatus"));//攻略审核状态
				str.setThereason(rs.getString("thereason"));//未通过攻略的原因
				Scene scene = new Scene();
				scene.setScenename(rs.getString("scenename"));//所对应景点的名称
				scene.setId(rs.getInt("scene.id"));//所对应景点的id
				str.setScene(scene);
				writedstr.add(str);
			}
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
		return writedstr;
    }
    
    
    //查询出此用户上传的攻略总数（已审核、未审核、审核不通过）
    public int getTheCountOfWrited(int userid) {
    	Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		int count =0;
		
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql = "select count(id) as sccount from strategy where userid=? and fakedelete=0";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getInt("sccount");
			} 
			
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
		return count;
		
    }
	
}











